package com.yj.ccs.abs.web.assetData.mapper;

import com.yj.ccs.abs.web.assetData.entity.AssetTop5;
import com.yj.ccs.abs.web.assetData.entity.BillAnalysis;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * Created by zhengguanyong on 2018/3/28.
 */
@Repository
@Mapper
public interface AssetDataMapper {

    @Select("select count(*) from contract where type=2")
    int countExistenceContract();  //存续合同数

    @Select("select count(*) from (select * from contract where type=2 group by buyer) a")
    int countExistenceContractBuyer();  //债务人

    @Select("select count(*) from asset")
    int countBaseAsset();  //基础资产笔数

    @Select("select ifnull(sum(money_sum),0) from asset")
    BigDecimal sumAssetMoney();  //应收帐款总金额

    @Select("select ifnull(sum(available_money),0) from asset")
    BigDecimal sumAssetAvailableMoney();  //用来算应收账款余额

    @Select("select ifnull(sum(money_sum),0) from asset where level=2")
    BigDecimal sumOverdueAssetMoney();  //逾期总金额

    @Select("select a.number,a.money_sum,a.available_money,cl.credit_day,if(datediff(a.predict_date,curdate())>0,datediff(a.predict_date,curdate()),0) remain_days from asset a,contract ct,client cl where a.id=ct.asset_id and ct.type=2 and ct.buyer=cl.name order by a.money_sum desc limit 5")
    List<AssetTop5> assetTop5List();  //资产TOP5

    @Select("select ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset where status!=6 and datediff(predict_date,curdate()) between #{min_day} and #{max_day}")
    BillAnalysis billAnalysis(@Param("min_day") int min_day,@Param("max_day") int max_day);

    @Select("select ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset where status!=6")
    BillAnalysis forBillAnalysis();

    @Select("select ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset ")
    BillAnalysis forAnalysisTotal();

    @Select("select ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset a,contract ct,client cl where a.id=ct.asset_id and ct.type=2 and ct.buyer=cl.name and cl.credit_day between #{min_day} and #{max_day}")
    BillAnalysis billCycle(@Param("min_day") int min_day,@Param("max_day") int max_day);

    @Select("select shadow_rating,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count " +
            "from asset a,contract ct,client cl " +
            "where a.id=ct.asset_id and ct.type=2 and ct.buyer=cl.name and cl.shadow_rating is not null " +
            "group by shadow_rating order by shadow_rating")
    List<BillAnalysis> shadowAnalysis();

    @Select("select industry,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count " +
            "from asset a,contract ct,client cl " +
            "where a.id=ct.asset_id and ct.type=2 and ct.buyer=cl.name and cl.industry is not null " +
            "group by industry order by sum_money_sum desc")
    List<BillAnalysis> industryAnalysis();

    @Select("select nature,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count " +
            "from asset a,contract ct,client cl " +
            "where a.id=ct.asset_id and ct.type=2 and ct.buyer=cl.name and cl.nature is not null " +
            "group by nature order by nature")
    List<BillAnalysis> natureAnalysis();

    @Select("select po.usage,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset a,property_ownership po where a.id=po.asset_id group by po.usage")
    List<BillAnalysis> usageAnalysis();

    @Select("select c.goods_type,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset a,contract c where a.id=c.asset_id and c.type=2 group by c.goods_type")
    List<BillAnalysis> goodsTypeAnalysis();

    @Select("select c.transport_type,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset a,contract c where a.id=c.asset_id and c.type=2 group by c.transport_type")
    List<BillAnalysis> transportTypeAnalysis();

    @Select("select level,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset group by level")
    List<BillAnalysis> levelAnalysis();

    @Select("select guarantee,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset group by guarantee")
    List<BillAnalysis> guaranteeAnalysis();

    @Select("select is_settled,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset group by is_settled")
    List<BillAnalysis> isSettledAnalysis();

    @Select("select accused,ifnull(sum(money_sum),0) sum_money_sum,ifnull(sum(available_money),0) sum_available_money,count(*) asset_count from asset group by accused")
    List<BillAnalysis> accusedAnalysis();
}



